package cn.com.chec.en.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.chec.en.model.domain.RightInfo;
import cn.com.chec.en.model.domain.RoleInfo;
import cn.com.chec.en.model.domain.UserInfo;
import cn.com.chec.en.util.SecurityMD5;

@Repository
public class UserInfoDao {
	protected static final Logger log = Logger.getLogger(NewsInfoDao.class);

	public boolean createRole(RoleInfo roleInfo, List<RightInfo> rights) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);

			String insertRole = "insert into tbl_role_i(role_name,role_desc) values(?,?)";

			PreparedStatement pstmtRole = conn.prepareStatement(insertRole);
			pstmtRole.setString(1, roleInfo.getRoleName());
			pstmtRole.setString(2, roleInfo.getRoleDesc());
			pstmtRole.execute();

			String lastIdSQL = "select last_insert_id() as lastId";
			int lastId = 0;
			PreparedStatement pstmtLastId = conn.prepareStatement(lastIdSQL);
			ResultSet rs = pstmtLastId.executeQuery();
			if (rs.next()) {
				lastId = rs.getInt("lastId");
			}

			String insertRight = "insert into tbl_right_i(right_roleid_fk,right_value) values(?,?)";

			PreparedStatement pstmtRight = conn.prepareStatement(insertRight);
			for (RightInfo ri : rights) {
				pstmtRight.setInt(1, lastId);
				pstmtRight.setString(2, ri.getRightValue());
				pstmtRight.addBatch();
			}
			pstmtRight.executeBatch();

			conn.commit();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean updateRole(RoleInfo role, List<RightInfo> rights) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);

			String updateRole = "update tbl_role_i set role_name = ?,role_desc = ?  where role_id = ? ";
			PreparedStatement pstmtRole = conn.prepareStatement(updateRole);
			pstmtRole.setString(1, role.getRoleName());
			pstmtRole.setString(2, role.getRoleDesc());
			pstmtRole.setInt(3, role.getRoleId());
			pstmtRole.executeUpdate();

			String deleteRigth = "delete from tbl_right_i where right_roleid_fk = ?";
			PreparedStatement pstmtDelRight = conn
					.prepareStatement(deleteRigth);
			pstmtDelRight.setInt(1, role.getRoleId());
			pstmtDelRight.execute();

			String insertRight = "insert into tbl_right_i(right_roleid_fk,right_value) values(?,?)";

			PreparedStatement pstmtRight = conn.prepareStatement(insertRight);
			for (RightInfo ri : rights) {
				pstmtRight.setInt(1, role.getRoleId());
				pstmtRight.setString(2, ri.getRightValue());
				pstmtRight.addBatch();
			}
			pstmtRight.executeBatch();

			conn.commit();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean createUser(UserInfo userInfo) {
		boolean result = false;
		if (userInfo.getRoleId() == 0)
			return false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();

			String insertUser = "insert into tbl_user_i(user_name,user_password,user_realname,user_deptname,user_telphone,user_mobilephone,user_email,user_roleid_fk) values(?,?,?,?,?,?,?,?)";

			PreparedStatement pstmtRole = conn.prepareStatement(insertUser);
			int i = 0;
			i++;
			pstmtRole.setString(i, userInfo.getUsername());
			i++;
			pstmtRole.setString(i, userInfo.getPassword());
			i++;
			pstmtRole.setString(i, userInfo.getRealName());
			i++;
			pstmtRole.setString(i, userInfo.getDeptName());
			i++;
			pstmtRole.setString(i, userInfo.getTelphone());
			i++;
			pstmtRole.setString(i, userInfo.getMobilephone());
			i++;
			pstmtRole.setString(i, userInfo.getEmail());
			i++;
			pstmtRole.setInt(i, userInfo.getRoleId());
			pstmtRole.execute();

			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public UserInfo getUser(int userId) {
		UserInfo user = new UserInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select u.*,ro.* from tbl_user_i u,tbl_role_i ro where u.user_id = ? and u.user_roleid_fk = ro.role_id limit 0,1 ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				user.setId(rs.getInt("user_id"));
				user.setUsername(rs.getString("user_name"));
				user.setPassword(rs.getString("user_password"));
				user.setRealName(rs.getString("user_realname"));
				user.setDeptName(rs.getString("user_deptname"));
				user.setTelphone(rs.getString("user_telphone"));
				user.setMobilephone(rs.getString("user_mobilephone"));
				user.setEmail(rs.getString("user_email"));
				user.setRoleId(rs.getInt("user_roleid_fk"));

				RoleInfo role = getRoleInfoByRoleId(user.getRoleId());
				user.setRoleInfo(role);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	public List<UserInfo> getAllUserName() {
		List<UserInfo> list = new ArrayList<UserInfo>();
		UserInfo user = null;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select user_id,user_name,user_realname,user_deptname from tbl_user_i ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new UserInfo();
				user.setId(rs.getInt("user_id"));
				user.setUsername(rs.getString("user_name"));
				user.setRealName(rs.getString("user_realname"));
				user.setDeptName(rs.getString("user_deptname"));
				list.add(user);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public RoleInfo getRoleInfoByRoleId(int roleId) {
		RoleInfo role = new RoleInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();

			String sql = "select * from tbl_role_i where role_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roleId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				role.setRoleId(rs.getInt("role_id"));
				role.setRoleName(rs.getString("role_name"));
				role.setRoleDesc(rs.getString("role_desc"));

				List<RightInfo> list = getRightsByRoleId(role.getRoleId());

				role.setRights(list);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return role;
	}

	public List<RightInfo> getRightsByRoleId(int roleId) {
		List<RightInfo> list = new ArrayList<RightInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();

			String sql = "select * from tbl_right_i where right_roleid_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roleId);
			ResultSet rs = pstmt.executeQuery();
			RightInfo r = null;
			while (rs.next()) {
				r = new RightInfo();
				r.setRoleId(roleId);
				r.setRightId(rs.getInt("right_id"));
				r.setRightValue(rs.getString("right_value"));
				list.add(r);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<String> getRightsStringByRoleId(int roleId) {
		List<String> list = new ArrayList<String>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();

			String sql = "select * from tbl_right_i  where right_roleid_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roleId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("right_value"));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<RoleInfo> getRoleList(int start, int num) {
		List<RoleInfo> list = new ArrayList<RoleInfo>();
		RoleInfo role = null;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_role_i limit ?,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, num);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				role = new RoleInfo();
				role.setRoleId(rs.getInt("role_id"));
				role.setRoleName(rs.getString("role_name"));
				role.setRoleDesc(rs.getString("role_desc"));

				List<RightInfo> ris = getRightsByRoleId(role.getRoleId());
				role.setRights(ris);

				list.add(role);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getRoleCount() {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(*) as count from tbl_role_i";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public List<UserInfo> getUserListByRoleId(int roleId, int start, int num) {
		List<UserInfo> list = new ArrayList<UserInfo>();
		UserInfo user = null;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select u.*,ro.* from tbl_user_i u,tbl_role_i ro where  u.user_roleid_fk = ro.role_id "
					+ (roleId > 0 ? " and u.user_roleid_fk = ? " : "")
					+ " limit ? ,? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			if (roleId > 0) {
				i++;
				pstmt.setInt(i, roleId);
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);

			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new UserInfo();
				user.setId(rs.getInt("user_id"));
				user.setUsername(rs.getString("user_name"));
				user.setPassword(rs.getString("user_password"));
				user.setRealName(rs.getString("user_realname"));
				user.setDeptName(rs.getString("user_deptname"));
				user.setTelphone(rs.getString("user_telphone"));
				user.setMobilephone(rs.getString("user_mobilephone"));
				user.setEmail(rs.getString("user_email"));
				user.setRoleId(rs.getInt("user_roleid_fk"));

				RoleInfo role = getRoleInfoByRoleId(user.getRoleId());
				user.setRoleInfo(role);

				list.add(user);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getUserCountByRoleId(int roleId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as count from tbl_user_i "
					+ (roleId > 0 ? " where user_roleid_fk = ? " : "");
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (roleId > 0) {
				pstmt.setInt(1, roleId);
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public boolean updateUser(UserInfo userInfo) {
		boolean result = false;
		if (userInfo.getId() == 0)
			return false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String insertUser = "update tbl_user_i set  user_password = ? ,user_realname = ?,user_deptname=? ,user_telphone = ? ,user_mobilephone = ? ,user_email = ? ,user_roleid_fk = ?  where user_id = ?";

			PreparedStatement pstmtRole = conn.prepareStatement(insertUser);
			int i = 0;
			i++;
			pstmtRole.setString(i, userInfo.getPassword());
			i++;
			pstmtRole.setString(i, userInfo.getRealName());
			i++;
			pstmtRole.setString(i, userInfo.getDeptName());
			i++;
			pstmtRole.setString(i, userInfo.getTelphone());
			i++;
			pstmtRole.setString(i, userInfo.getMobilephone());
			i++;
			pstmtRole.setString(i, userInfo.getEmail());
			i++;
			pstmtRole.setInt(i, userInfo.getRoleId());
			i++;
			pstmtRole.setInt(i, userInfo.getId());
			pstmtRole.executeUpdate();

			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean resetUserPwd(String newsPwd, int userId) {
		boolean result = false;
		if ((userId == 0) || (StringUtils.isBlank(newsPwd)))
			return false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String insertUser = "update tbl_user_i set  user_password = ?  where user_id = ?";

			PreparedStatement pstmtRole = conn.prepareStatement(insertUser);
			int i = 0;
			i++;
			pstmtRole.setString(i, SecurityMD5.crypt(newsPwd));
			i++;
			pstmtRole.setInt(i, userId);
			pstmtRole.executeUpdate();

			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean delUser(int userId) {
		boolean result = false;
		if (userId == 0)
			return false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String insertUser = "delete from  tbl_user_i where user_id = ?";
			PreparedStatement pstmtRole = conn.prepareStatement(insertUser);
			int i = 0;
			i++;
			pstmtRole.setInt(i, userId);
			pstmtRole.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public UserInfo userLogin(String username, String password) {
		UserInfo userInfo = new UserInfo();
		try {
			int userId = 0;
			Connection conn = DBFactory.getInstance().getConnection();
			String selectUser = "select user_id from  tbl_user_i where user_name = ? and user_password = ? limit 0,1 ";
			PreparedStatement pstmt = conn.prepareStatement(selectUser);
			int i = 0;
			i++;
			pstmt.setString(i, username);
			i++;
			pstmt.setString(i, SecurityMD5.crypt(password));
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				userId = rs.getInt("user_id");
			}

			if (userId == 0) {
				userInfo = null;
			} else {
				userInfo = getUser(userId);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userInfo;
	}

	public UserInfo userLoginNoMd5(String username, String password) {
		UserInfo userInfo = new UserInfo();
		try {
			int userId = 0;
			Connection conn = DBFactory.getInstance().getConnection();
			String selectUser = "select user_id from  tbl_user_i where user_name = ? and user_password = ? limit 0,1 ";
			PreparedStatement pstmt = conn.prepareStatement(selectUser);
			int i = 0;
			i++;
			pstmt.setString(i, username);
			i++;
			pstmt.setString(i, password);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				userId = rs.getInt("user_id");
			}

			if (userId == 0) {
				userInfo = null;
			} else {
				userInfo = getUser(userId);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userInfo;
	}
}